Amazon QLDBのジャーナルデータがJSONエクスポート出来るようになったので、QuickSightで集計してみた

いわさです。

Amazon Quantum Ledger Database(QLDB)ではジャーナルデータのエクスポート機能があります。従来はAmazon独自のIon形式のみサポートされていましたが、先日JSON形式での出力に対応しました。

Amazon QLDB now supports JSON output format for exports

これによって、GlueやAthenaなどで取り扱いやすい形式になりましたので本日は集計したジャーナルデータをQuickSightで表示してみました。

エクスポート

データの用意

まず、ジャーナルデータを用意する必要がありますが、こちらについてはQLDBコンソールでロードできるサンプルデータを使って、いくつかのクエリを実行している前提とします。
詳細は公式ドキュメントのGetting started with the Amazon QLDB console - Amazon Quantum Ledger Database (Amazon QLDB)をご参照ください。

ちなみに、上記手順では一部クエリ誤りがあるようでした。私はうまくいかなかったので以下のクエリで代替しています。

SELECT * FROM Person AS p, DriversLicense AS l
WHERE p.GovId = l.LicenseNumber

↓

SELECT * FROM Person AS p, DriversLicense AS l
WHERE p.GovId = l.LicensePlateNumber

エクスポート操作

エクスポート先はS3バケットとなりますので事前に準備しておきます。
エクスポートはジョブを発行する形となります。この記事ではコンソール画面から手動でジョブを作成しましたが、実際には定期的に自動でジョブを作成する流れになるのではないかと思います。

エクスポート形式でJSONが選択できるようになっているので、選択しましょう。

ジョブの実行が完了すると以下の形式でS3バケットへ出力されていると思います。

{"blockAddress":{"strandId":"294jgIvicl39p2SvfmPRJK","sequenceNo":0},"transactionId":"8fcbL1p5NSW6QbLgBh4C0b","blockTimestamp":"2022-01-07T20:02:59.421Z","blockHash":"FSGrXi6bWc5LaKI6cYCOj+doyFed1ZRD9YJhgY13Ujw=","entriesHash":"FSGrXi6bWc5LaKI6cYCOj+doyFed1ZRD9YJhgY13Ujw=","previousBlockHash":"","entriesHashList":["jtMwLm8JNJpWajXYp5Zw4fPQP2lyDC2UxkLScA+UBXs=","fHDFFjhC6OpK4+k+neriLuqW5NPa9dePB5iN58tKZ/U=","NAH5hYT0YwM3JOg0mCQmh9QCvrh64p02O8R+2mkYsuk=","g2em3Vcy7q2NGC1ZNPsYZewYqAcbdEjwlGmZnev2J3E="],"transactionInfo":{"statements":[{"statement":"SELECT * FROM information_schema.user_tables","startTime":"2022-01-07T20:02:59.168Z","statementDigest":"vrvWiTj+6MEi8QmaEflsEgf4p3SBk7HREoAXijhK9vc="}]},"revisions":[{"hash":"fHDFFjhC6OpK4+k+neriLuqW5NPa9dePB5iN58tKZ/U="}]}
{"blockAddress":{"strandId":"294jgIvicl39p2SvfmPRJK","sequenceNo":1},"transactionId":"4I9Lj5xmuFhKTBzpmH7NyO","blockTimestamp":"2022-01-07T20:03:00.599Z","blockHash":"miElBU0j+lpkxwYb9STUXrA/edtR90+V4wbo6f97EBI=","entriesHash":"W03l1RJIs+WdbAQzPVffYawWrp0NxGZpZy7ViRwzubY=","previousBlockHash":"FSGrXi6bWc5LaKI6cYCOj+doyFed1ZRD9YJhgY13Ujw=","entriesHashList":["W6h1V6w5lylpKQpL4tDJBN7MoV1vR/3VdKbNDxUE4fc=","","XXqIMWUYIl875A4ZSdnWbI7k3+n3qGxxEQo3EnUK3f4="],"transactionInfo":{"statements":[{"statement":"SELECT * FROM information_schema.user_tables","startTime":"2022-01-07T20:03:00.486Z","statementDigest":"vrvWiTj+6MEi8QmaEflsEgf4p3SBk7HREoAXijhK9vc="}]}}
{"blockAddress":{"strandId":"294jgIvicl39p2SvfmPRJK","sequenceNo":2},"transactionId":"FnQedDJwotJ0GYjKDcGxgr","blockTimestamp":"2022-01-07T20:03:38.391Z","blockHash":"zXsXRaNpUTt83twq2wqj+GQe+y6A3xwqyIG3shenKy4=","entriesHash":"kwZLcz03v4AvOMIrzyxLYrmm5OUrB+m080mCTo9ARdc=","previousBlockHash":"miElBU0j+lpkxwYb9STUXrA/edtR90+V4wbo6f97EBI=","entriesHashList":["1ul+oRXkMFRfJ1BnSlJJ3En7ugI5bN53ntR9wqYe6n4=","mgG7WdUqIZl+d33MSyoVHLrWzxVc+aQ17HtarfbFpYE=","OLxWSaTof5qs7oowv2Vj5U/CemHyRICJeh0fQJi0hJo=","JfnX/Ivmhj+HEbORVpiLKoqUdcTNwm09XCipk0MPAOA="],"transactionInfo":{"statements":[{"statement":"CREATE TABLE hogetable","startTime":"2022-01-07T20:03:38.199Z","statementDigest":"5QYAnRGRR97XPWap9X5MZCDX82puUPE8GIPwXAFi69s="}]},"revisions":[{"hash":"ZyEtAsD1N36x1ZND1ioB29+vlh4kfRA2PalaRFtBIWo="},{"hash":"xO+cZITNyFokbw10ZjE6MVYgSv40b6uKBj2csi/1cG4="}]}
...

取り込みと可視化

本日はジャーナルデータのうち、各トランザクションのPartiQLステートメントからクエリタイプを抽出して、グラフで表示したいと思います。

Athenaからアクセス

まずは上記ジャーナルをAthenaでクエリできるようにします。
また、QuickSightからアクセスしやすいようにViewも作成しておきます。

CREATE EXTERNAL TABLE IF NOT EXISTS `hoge-ledger`.`hoge-ledger-manual3` (
  `blockAddress` struct<
    `strandId`:string,
    `sequenceNo`:bigint
    >,
  `transactionId` string,
  `blockTimestamp` string,
  `blockHash` string,
  `entriesHash` string,
  `previousBlockHash` string,
  `entriesHashList` array<string>,
  `transactionInfo` struct<
    `statements`:array<struct<
        `statement`:string,
        `startTime`:string,
        `statementDigest`:string
        >>
    >
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' 
WITH SERDEPROPERTIES (
  'serialization.format' = '1'
) LOCATION 's3://2022010ledger/2022/01/07/20/'
TBLPROPERTIES ('has_encrypted_data'='false');

データを読み込めていますね。
エクスポートデータの形式の詳細は以下をご確認ください。この記事ではdocumentsやrevsionsを無視していますのでご利用の際は含めてください。

Journal export output in QLDB - Amazon Quantum Ledger Database (Amazon QLDB)

QuickSightのSPICEへロードさせるために必要なデータを絞ったViewも作成しておきます。

CREATE OR REPLACE VIEW view_name AS
SELECT 
    blocktimestamp, 
    hogestatement.statement
FROM "hoge-ledger"."hoge-ledger-table"
CROSS JOIN UNNEST(transactioninfo.statements) as t(hogestatement)

QuickSightで可視化

あとはデータセットの作成でAthenaで作成したViewを選択するだけなのですが、集計用に計算フィールドだけ追加しておきたいと思います。
splitでクエリタイプだけ取得しました。statementに入ってくる文字列をすべて把握出来ていないのでもしかすると雑かもしれませんので参考程度で。

このグラフに意味があるかはさておき、QLDBのジャーナルデータを集計して可視化することが出来ました。
S3やQuickSightのSPICEに取り込んでおくと、都度QLDBでHistoryクエリを実行しなくて済むので良いですね。
また、QLDBのベストプラクティスではジャーナル参照はIDや日付など極力絞り込んでくれ、となっているのでその観点からもエクスポートしたデータをQLDBの外で参照できるのは良いことでしょう。

さいごに

本日はQLDBの新機能JSONエクスポートを試してみました。

今回はサンプルデータだったのでクエリタイプを集計しただけですが、ジャーナルデータはデータやテーブルの改訂内容が細かく出力されるので、実際にはドメインデータに近いところを集計することも出来ます。
従来のIon形式より取り扱いやすくなったと思いますので是非試してみてください。

ちなみに、QLDBでは従来でもストリーム連携機能がありました。
リアルタイムの分析では引き続きそちらをご利用頂くのが良いです。

一方でQLDBストリームはいくつか注意点もあるので、リアルタイム性が要求されない分析であればエクスポート機能をご利用頂くのが良いかと思います。